package com.ouc.yyi.test05;

import java.sql.*;


public class Test05 {
    // JDBC 驱动名 及数据库 URL
    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    static final String DB_URL = "jdbc:mysql://localhost:3306/homework";

    // 数据库的用户名与密码，需要根据自己的设置
    static final String USER = "root";
    static final String PASS = "123";

    public static void main(String[] args) throws Exception {
        Connection conn = null;
        Statement stmt = null;
        try {
            // 注册 JDBC 驱动
            // 把Driver类装载进jvm
            Class.forName("com.mysql.jdbc.Driver");

            // 打开链接、
            conn =  DriverManager.getConnection(DB_URL, USER, PASS);
            System.out.println("数据库连接成功....");

            // 执行查询
            stmt =  conn.createStatement();
            String sql = "SELECT * FROM `user`";
            ResultSet rs = stmt.executeQuery(sql);

            while (rs.next()) {
                // 通过字段检索
                int id = rs.getInt("id");
                String username = rs.getString("username");
                String password = rs.getString("password");
                String email = rs.getString("email");
                // 输出数据
                System.out.print("ID: " + id);
                System.out.print(", username: " + username);
                System.out.print(",password: " + password);
                System.out.print(", email：" + email);
                System.out.print("\n");
            }
            //更新update
            String update_sql = "update  `user` set username='yangyi',password='123',email='yyiouc@126.com' user_phone =  where id=4";
            stmt.execute(update_sql);

            //增加insert
            String inset_sql = "insert into `user` (username, password, email) values('xiaoming','123','yangyi521@163.com')";
             stmt.executeUpdate(inset_sql); //执行sql语句

            //删除 delete
            String delete_sql = "delete from `user` where id=1";
            stmt.execute(delete_sql);

            // 完成后关闭
            rs.close();
            stmt.close();
            conn.close();
        } catch (SQLException se) {
            // 处理 JDBC 错误
            se.printStackTrace();
        } catch (Exception e) {
            // 处理 Class.forName 错误
            e.printStackTrace();
        } finally {
            // 关闭资源
            if (stmt != null)
                stmt.close();
            if (conn != null)
                conn.close();
        }
        System.out.println("6步法测试结束!");
    }
}